update query date function

Question

I create update query for one table. I need created date from the old database. But I don't know how to do it?

My query is,

select 'update organizations set createddate='+CAST(RegisteredDate as varchar(50))+'''where Id='+cast(organizationid as varchar(50))+'' from OrganizationRegisteredDetails

when i use this query my result is,

update organizations set createddate=Aug 19 2009 10:20AM'where Id=1

But I want the result,

createddate = 8/19/2009 only. I don't want time.

So how to do this?

Is this possible?

**RTFM!** [Here's an **extensive** list of all `CAST` styles available](http://msdn.microsoft.com/en-us/library/ms187928.aspx) - pick the one most suitable to you! I would recommend using the **ISO-8601** format (style 112) since it's the only reliable one that works for all regional/language settings in SQL Server — marc_s, Apr 12 '13 at 06:11
If the two fields are of the same data type `date` or `datetime` then you didn't need any conversion, dates are not stored in a specific format.\ — Mahmoud Gamal, Apr 12 '13 at 06:15

score 1 · Answer 1 · answered Apr 12 '13 at 06:10

1

Convert the date to the appropriate format.

http://www.sql-server-helper.com/tips/date-formats.aspx

answered Apr 12 '13 at 06:10

Pleun

8,856
2
30
50

score 1 · Answer 2 · answered Apr 12 '13 at 06:11

1

you can use this for date only

SELECT CONVERT(VARCHAR(10),GETDATE(),111)

answered Apr 12 '13 at 06:11

Mayukh Roy

1,815
3
19
31

I would use style no. 112 (the ISO style) since it's the only one that will reliably work regardless of any regional, language or dateformat settings on your SQL Server – marc_s Apr 12 '13 at 06:13
thanks @marc_s for the focus. will consider that for sure – Mayukh Roy Apr 12 '13 at 06:17

score 0 · Accepted Answer · answered Apr 12 '13 at 06:18

0

You have missed to add a single quote before the value,

select 'update organizations set createddate='''
                                             ^^ here

and use CAST(RegisteredDate AS DATE) if you only want the date.

answered Apr 12 '13 at 06:18

John Woo

258,903
69
498
492

I have an error like"The data types varchar and date are incompatible in the add operator" – PoliDev Apr 12 '13 at 06:24
is `RegisteredDate` a date or a string? – John Woo Apr 12 '13 at 06:26
what is the format? is it `Aug 19 2009 10:20AM`? – John Woo Apr 12 '13 at 06:34
In database, this is the format 2009-08-19 10:20:00. when i used my query below what i add my question i got Aug 19 2009 10:20AM. But I want date type only. – PoliDev Apr 12 '13 at 06:36
1

how about `convert(varchar(15), RegisteredDate, 101)` – John Woo Apr 12 '13 at 06:38
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/28082/discussion-between-angdei-and-j-w) – PoliDev Apr 12 '13 at 06:44

update query date function

3 Answers3