Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Insert date in yyyy-mm format Expand / Collapse
Author
Message
Posted Sunday, September 22, 2013 2:57 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 1:05 PM
Points: 5, Visits: 7
Hi,

I'm trying to insert random date in yyyy-mm format to have expiry Credit card date column.
Haw can I do it? is it possible at all?
I know that I can sore the full (yyyy-mm-dd) and then to select with: convert(char(7),column_a,121)
but this is not the way I'm looking for .

Thanks
Post #1497238
Posted Sunday, September 22, 2013 3:35 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:50 PM
Points: 5,618, Visits: 25,235
This might be what you are looking for:

SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]

Result:
2013/09
]



If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read

Before posting a performance problem please read
Post #1497241
Posted Monday, September 23, 2013 12:43 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 1:05 PM
Points: 5, Visits: 7
If I want to select it's working but as I mentioned I'm trying to insert and because my column is a 'date type' when I'm using CONVERT(VARCHAR(7), GETDATE(), 111)-- AS [YYYY/MM]
I'm getting this error message:

Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


Do u have any suggestions?

Thanks for trying to help
Post #1497502
Posted Monday, September 23, 2013 1:31 PM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: 2 days ago @ 7:26 AM
Points: 4,390, Visits: 9,536
In order to insert data into a date data type - you need a valid date or string that can be converted into a date.

If you are looking for random dates - then wouldn't you want random days also? Or are you looking for random months only? If random months only, then just pick a day - either the 1st or 15th would be my recommendation.


Jeffrey Williams
Problems are opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #1497519
Posted Monday, September 23, 2013 1:47 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 1:05 PM
Points: 5, Visits: 7
I don't have a problem to get a random month or a day I just using this :

UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')

But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).

Post #1497531
Posted Monday, September 23, 2013 2:06 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 13,481, Visits: 12,337
golansimani (9/23/2013)
I don't have a problem to get a random month or a day I just using this :

UPDATE table_a
SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')

But the column that I'm trying to populate is a 'credit card expiry date' and it need to be '2015/01' (yyyy/mm).



Since your target column is not a datetime you can't use the results of DATEADD. That function returns a datetime. You would have to kludge this a bit more.

SET column_a = cast(year(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as char(4))
+ '/'
+ right('0' + cast(month(DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01')) as varchar(2)), 2)



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497542
Posted Monday, September 23, 2013 3:03 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 1:05 PM
Points: 5, Visits: 7
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.
Post #1497561
Posted Monday, September 23, 2013 3:10 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 13,481, Visits: 12,337
golansimani (9/23/2013)
Its not working , I got this message :
Msg 241, Level 16, State 1, Line 1
Conversion failed when converting date and/or time from character string.


What is the datatype of column_a? I suspect that column is actually a datetime based on your message.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497563
Posted Monday, September 23, 2013 3:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, November 24, 2013 1:05 PM
Points: 5, Visits: 7
Its a Date Data Type
Post #1497566
Posted Monday, September 23, 2013 3:19 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:02 AM
Points: 13,481, Visits: 12,337
golansimani (9/23/2013)
Its a Date Data Type


Then the code you posted should work fine.

SET column_a= DATEADD(DAY, ABS(CHECKSUM(NEWID()) % 3650), '2015-01-01') 

Remember there is not such thing as a format for date columns.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1497567
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse