Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
adb11a
Starting Member
3 Posts |
 Posted - 2012-06-16 : 05:27:28
|
| Hi,I'm ok with VBA but very new to SQL Server. I have a text file .CSV with a date in the format of 41006 for today (16/06/12). How do I convert it in a select query? I've tried the following but nothing is extracted.select [Collection Identifer],[Date of service formatted], convert(varchar,[Date of service formatted],103) 'dd/mm/yy'from dbo.SampleDataTestgoAndrew Buchter |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-16 : 07:37:06
|
quote:
Originally posted by adb11a
Hi,I'm ok with VBA but very new to SQL Server. I have a text file .CSV with a date in the format of 41006 for today (16/06/12). How do I convert it in a select query? I've tried the following but nothing is extracted.select [Collection Identifer],[Date of service formatted], convert(varchar,[Date of service formatted],103) 'dd/mm/yy'from dbo.SampleDataTestgoAndrew Buchter
What is the logic you would use to convert if you had to do it on paper? |
 |
|
|
adb11a
Starting Member
3 Posts |
Posted - 2012-06-16 : 08:32:24
|
| Not sure what you mean by 'on paper' but if you mean in VBA something like the following in Excel would provide the result I'm after MsgBox (Format(ActiveCell, "dd/mm/yy"))Andrew Buchter |
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2012-06-16 : 09:02:36
|
i am a bit confused. You said you have a text file in CSV. Have you imported that into the SampleDataTest table ?
KH[spoiler]Time is always against us[/spoiler] |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2012-06-16 : 09:36:48
|
quote:
Originally posted by adb11a
Not sure what you mean by 'on paper' but if you mean in VBA something like the following in Excel would provide the result I'm after MsgBox (Format(ActiveCell, "dd/mm/yy"))Andrew Buchter
That is what I am asking. If I do what you described, it gives me 07/04/12. What is the logic you are using from that to get to 16/06/12?If it is indeed April 7, 2012 that 41006 represents, you can convert it as CAST(41006 AS DATETIME)-2 The "-2" is required because 0 in SQL Server represents Jan 1, 1900 but in Excel's OADate, 0 represents Dec 30, 1899. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2012-06-16 : 12:33:03
|
SELECT DATEADD(DAY, 41006, '18991230')
N 56°04'39.26"E 12°55'05.63" |
|
|
|
adb11a
Starting Member
3 Posts |
Posted - 2012-06-16 : 22:12:23
|
| Hi sunitabeck,You're right, I meant to use 41006, not 41076 and that would have given me 16/06/12.Anyway, I've now learnt a new function 'CAST' thanks to you. I'll reseach it a little more now but thanks to you I've been able to get it to work how I want.Thanks again.Andrew Buchter |
|
|
|
|
|
|
|
|
Convert int to date (dd/mm/yy)