December 10, 2009 at 9:34 am
Hello experts,
I’m having a little challenge in updating one of my table. I’m trying to capture create_date from sys.databases. when I try to update my table using the following code it
works fine no problems.
update #TempTotalDatabase
set DBCreationDate = create_date
from sys.databases
WHERE DBName = name
However create_date field gives me Date with time. I don’t need time. So I figured out a covert function which give me only date. Here is the code for that
select convert (varchar(10), create_date, 103) from sys.databases
I exactly need create_date in the above format. Now when I tried to update my table with the following code I end up having error. Here is the code and error.
update #TempTotalDatabase
set DBCreationDate = select convert (varchar(10), create_date, 103)
from sys.databases
WHERE DBName = name
ERROR:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Can somebody correct my mistake please?
Thanks a lot in advance.
December 10, 2009 at 9:36 am
mr_adeelalisyed (12/10/2009)
Hello experts,I’m having a little challenge in updating one of my table. I’m trying to capture create_date from sys.databases. when I try to update my table using the following code it
works fine no problems.
update #TempTotalDatabase
set DBCreationDate = create_date
from sys.databases
WHERE DBName = name
However create_date field gives me Date with time. I don’t need time. So I figured out a covert function which give me only date. Here is the code for that
select convert (varchar(10), create_date, 103) from sys.databases
I exactly need create_date in the above format. Now when I tried to update my table with the following code I end up having error. Here is the code and error.
update #TempTotalDatabase
set DBCreationDate = select convert (varchar(10), create_date, 103)
from sys.databases
WHERE DBName = name
ERROR:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'select'.
Can somebody correct my mistake please?
Thanks a lot in advance.
Remove the Select keyword before convert and try
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
December 10, 2009 at 9:48 am
Thanks a lot Bru, it worked. Now one more question. Since I just need date in 'dd/mm/yyyy' format should i use varchar(10) instead of smalldatetime?
December 10, 2009 at 10:25 am
Where are you using varchar(10) and smalldatetime, I dont see the DDL stmts.
But yes, you can use Varchar(10) to store in the format that you wanted to.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply