VB6 date vs SQL Server date
-
To simplify date conversion i was adviced to use to integer converted dates i.s.o. the datetime type. Now i have the integer 37954 which resembles 01-12-2003 (dd-mm-yyyy) select cast(37954 as datetime) as startdate But VB6 thinks it is 29-11-2003 ?Format(37954, "dd-mm-yyyy") How is this possible? What am i doing wrong? Grtz, Guus
-
To simplify date conversion i was adviced to use to integer converted dates i.s.o. the datetime type. Now i have the integer 37954 which resembles 01-12-2003 (dd-mm-yyyy) select cast(37954 as datetime) as startdate But VB6 thinks it is 29-11-2003 ?Format(37954, "dd-mm-yyyy") How is this possible? What am i doing wrong? Grtz, Guus
Doing this will give you a major headache. Use the
datetimedata type. If you need to use date literals, specify them in the ISO format: 'yyyymmdd' (e.g. today would be '20040401'). Stability. What an interesting concept. -- Chris Maunder -
To simplify date conversion i was adviced to use to integer converted dates i.s.o. the datetime type. Now i have the integer 37954 which resembles 01-12-2003 (dd-mm-yyyy) select cast(37954 as datetime) as startdate But VB6 thinks it is 29-11-2003 ?Format(37954, "dd-mm-yyyy") How is this possible? What am i doing wrong? Grtz, Guus
Have you tried storing dates as strings? Easy to display in the format you want and easy to convert correctly to date/time format for any calculations that you need to perform on them. Jon
-
Doing this will give you a major headache. Use the
datetimedata type. If you need to use date literals, specify them in the ISO format: 'yyyymmdd' (e.g. today would be '20040401'). Stability. What an interesting concept. -- Chris Maunder -
Thanks for the good advice but it doesn't answer my question. How is it possible that an integer converted to a date results in two different dates on VB vs SQL Server?
VB6's built-in dates use OLE's DATE format - an 8-byte floating point number based on 30 December 1899 = 0.0, with the time represented as a fraction. SQL Server's datetime data type is made up of two four-byte signed integers, based on 1 January 1900. The documentation reads: "Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight." When converting to and from integers, both VB6 and SQL Server simply use their own internal representations, which is why they produce different values. Stability. What an interesting concept. -- Chris Maunder
-
VB6's built-in dates use OLE's DATE format - an 8-byte floating point number based on 30 December 1899 = 0.0, with the time represented as a fraction. SQL Server's datetime data type is made up of two four-byte signed integers, based on 1 January 1900. The documentation reads: "Values with the datetime data type are stored internally by Microsoft SQL Server as two 4-byte integers. The first 4 bytes store the number of days before or after the base date, January 1, 1900. The base date is the system reference date. Values for datetime earlier than January 1, 1753, are not permitted. The other 4 bytes store the time of day represented as the number of milliseconds after midnight." When converting to and from integers, both VB6 and SQL Server simply use their own internal representations, which is why they produce different values. Stability. What an interesting concept. -- Chris Maunder
-
Have you tried storing dates as strings? Easy to display in the format you want and easy to convert correctly to date/time format for any calculations that you need to perform on them. Jon
Hi, jonathan15 I hope you can help me... I store an elapsed time value as a string (mm:ss) in my SQL database. When I query the data from Excel, how can I calculate an average from this data? Would it help if I made the SQL datatype some kind of time value? Thanks in advance for any advice you may provide. 'til next we type... HAVE FUN!! -- Jesse
-
Hi, jonathan15 I hope you can help me... I store an elapsed time value as a string (mm:ss) in my SQL database. When I query the data from Excel, how can I calculate an average from this data? Would it help if I made the SQL datatype some kind of time value? Thanks in advance for any advice you may provide. 'til next we type... HAVE FUN!! -- Jesse
Jesse, Depends on what you want to do and how you want to get the information into excel really. If you are doing a query to get the raw data into excel and average the results from there you could use something like the following in your Query "select table1.duration, (cast(left(table1.duration,2) as int) *60)+cast((right(table1.duration,2))as int) as seconds from table1" assuming the duration is MM:SS, this will add a field called 'Seconds' which would be a numeric value to your query. it would then be simple to average the seconds using an excel function. Jonathan