change date from yymmdd to ddmmyy
-
I Have a date in a flat file which is in yymmdd i want to bring this into sql as ddmmyy how can i do this.
-
1st, date must be stored in database in datetime datatype. No format involved.
2nd, open BOL and type in "Index" keyword CAST, press Enter twice and read about converting strings to datetime.
Pay attention to style 12.
_____________
Code for TallyGenerator -
To elaborate on Sergiy's response (which is 100% correct btw), it's common for many people from the dbase / flat-file world to store dates as strings in the database. Commonly they are stored in yyyymmdd format to allow for easy sorting.
Dates are better handled as a floating point number where the integral part is the number of days past a certain nominated date (eg 1st Jan 1900 in SQL Server, although I more often see 1899-12-30 too??) and the fractional part is the time of day - eg .5 represents midday.
Thus Sergiy is suggesting that rather than importing the dates and storing in a certain format, you should instead tell SQL Server the source date format string so that it can convert it to a native date. You'll get many benefits from doing this (eg the date functions will work!).
If you are stuck on the import step, import the date data as a string unchanged and then do a table update to transfer the data from the temporary date string column to the permanent date column.
Cheers...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply